package com.rybbaby.tss.front.tssClasses.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.StringUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssClasses.dao.ITssClassesDao;
import com.rybbaby.tss.front.tssClasses.entity.TssClasses;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author 雷春琼
 * @description 班级表数据库操作实现
 * @createDate 2017-08-09 下午11:50:14
 */
@Repository
public class TssClassesDaoImp extends GenericDao<TssClasses> implements ITssClassesDao {

    @Override
    public List<Map<String, Object>> batchQueryById(String id) {
        String idsStr = "'" + StringUtils.join(id.split(","), "','") + "'";
        String sql = "SELECT\n" +
                "\tc.id,\n" +
                "\tc.startDate,\n" +
                "\tc.endDate,\n" +
                "\tc.startTime,\n" +
                "\tc.endTime,\n" +
                "\tc.weekDay,\n" +
                "\tts. realName name,\n" +
                "\tc.expendClassHour,\n" +
                "\tc.pitchNumber,\n" +
                "\tcs. NAME courseName,\n" +
                "\tcr. NAME roomName\n" +
                "FROM\n" +
                "\ttss_classes c\n" +
                "LEFT JOIN sys_admin_user ts ON ts.id = c.adminUserId\n" +
                "LEFT JOIN tss_class_room cr ON cr.id = c.roomId\n" +
                "LEFT JOIN tss_course tc ON tc.id = c.courseId\n" +
                "LEFT JOIN sys_course_system cs ON cs.id = tc.systemId\n" +
                "WHERE\n" +
                "\tc.id IN (" + idsStr + ")";
        return this.findForJdbc(sql, null);
    }

    /**
     * 停用,恢复班级
     *
     * @param targetDateId
     * @param isBlockUp
     * @return
     */
    @Override
    public Integer updateClassByIsBlockUp(String targetDateId, Boolean isBlockUp) {
        return this.executeSql("update tss_classes set updateTime=Now(),isBlockUp=? where id=?", new Object[]{isBlockUp, targetDateId});
    }

    @Override
    public Integer getExpendClassHour(String lessonId) {
        String sql = "select expendClassHour from tss_classes where id=(select classId from tss_class_lessons where id=?)";

        Map<String, Object> oneForJdbc = this.findOneForJdbc(sql, new Object[]{lessonId});
        Integer expendClassHour;
        if (oneForJdbc != null) {
            expendClassHour = Integer.valueOf(oneForJdbc.get("expendClassHour") + "");
        } else {
            expendClassHour = 0;
        }
        return expendClassHour;
    }

    @Override
    public PageBean attendancePageList(String notAttendance, int pageNo, int pageSize) {
        String notAttendanceSql = "";

        if (notAttendance.equals("0")) {
            notAttendanceSql = "WHERE B.sumNotAttendance > 0";
        } else {
            notAttendanceSql = "WHERE B.sumNotAttendance < 1";
        }

        String sql = "SELECT B.*\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\tA.*,\n" +
                " COUNT(\n" +
                "\t\ttss_member_check_attendance.id\n" +
                "\t) AS sumShouldAttendAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '0' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumNotAttendance,\n" +
                "\tCOUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '1' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tEND\n" +
                "\t) AS sumRealAttendance,\n" +
                "COUNT(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN attendenceStatus = '2' THEN\n" +
                "\t\t\tattendenceStatus\n" +
                "\t\tELSE 0\n" +
                "\t\tEND\n" +
                "\t) AS sumMakeUpAttendance,\n" +
                "\ttss_member_check_attendance.type,\n" +
                "\ttss_member_check_attendance.createTime\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\ttss_classes.id,\n" +
                "\t\t\ttss_class_lessons.id AS lessonsId,\n" +
                "\t\t\ttss_class_lessons.classTime,\n" +
                "\t\t\ttss_class_lessons.startTime,\n" +
                "\t\t\ttss_class_lessons.endTime,\n" +
                "\t\t\ttss_classes.`name` AS className,\n" +
                "\t\t\ttss_class_room.`name` AS roomName,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName\n" +
                "\t\tFROM\n" +
                "\t\t\ttss_class_lessons\n" +
                "\t\tLEFT JOIN tss_classes ON tss_class_lessons.classId = tss_classes.id\n" +
                "\t\tLEFT JOIN tss_class_room ON tss_classes.roomId = tss_class_room.id\n" +
                "\t\tLEFT JOIN sys_admin_user ON tss_classes.adminUserId = sys_admin_user.id\n" +
                "\t\tWHERE\n" +
                "\t\t\tto_days(\n" +
                "\t\t\t\ttss_class_lessons.classTime\n" +
                "\t\t\t) < to_days(now())\n" +
                "\t\tAND tss_class_lessons.`status` = 1\n" +
                "\t\tAND tss_class_lessons.`adminUserId` in ('2c9180865da5c2bd015daaf0427b006e','4028828160f768840160f79c6e3400ce','4028828260b0fdc20160b1a22f400031','4028828260b0fdc20160b1a313f70038','4028828260b0fdc20160b1a36d380041','4028828260b0fdc20160b1a3a93b0048','4028828260b0fdc20160b1a5bde50066','4028828260b0fdc20160b1a64d44006e','4028828260b0fdc20160b1a6d1a90079','4028828260b0fdc20160b1a75ad50088','B088608C5BD74E6D8BE3FA288E1EE826')\n" +
                "\t) A\n" +
                "LEFT JOIN tss_member_attend_class_record ON A.lessonsId = tss_member_attend_class_record.lessonsId\n" +
                "LEFT JOIN tss_member_check_attendance ON tss_member_attend_class_record.id = tss_member_check_attendance.attendClassId\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.isDelete = '0'\n" +
                "GROUP BY\n" +
                "\tA.lessonsId\n" +
                "ORDER BY A.classTime\n" +
                ")B\n" +
                notAttendanceSql;
        System.out.println(sql);
        return this.pageList(sql, pageNo, pageSize);
    }

    @Override
    public int getStudentCount(String classId) {
        int result = 0;
        String sql = "SELECT COUNT(1) count FROM (SELECT 1 FROM tss_member_attend_class_record WHERE lessonsId in(select id from tss_class_lessons WHERE classId=?) GROUP BY memberId) tempTab";
        Map<String, Object> oneForJdbc = this.findOneForJdbc(sql, new Object[]{classId});
        if (oneForJdbc != null && !oneForJdbc.isEmpty()) {
            Object count = oneForJdbc.get("count");
            result = count == null ? result : Integer.parseInt(count.toString());
        }
        return result;
    }

    @Override
    public List<TssClasses> getStudentClasses(String studentId) {
        String sql = "SELECT * FROM tss_classes WHERE id IN(SELECT classId FROM tss_class_lessons WHERE id IN (SELECT lessonsId FROM tss_member_attend_class_record WHERE memberId=?))";
        return this.findListBySql(sql, TssClasses.class, studentId);
    }

    @Override
    public Map<String, Object> verificationClass(String startDate, String endDate, String startTime, String endTime, String roomId, String weekDay, String empAdminUerId,String schoolId,Integer type) {
        StringBuffer buf=new StringBuffer();
        StringBuffer sql=new StringBuffer();


            String startDateSql="SELECT\n" +
                    "   min(tc.startDate) startDate\n" +
                    "   FROM\n" +
                    "   tss_classes tc\n" +
                    "   LEFT JOIN sys_admin_user sau ON sau.id = tc.adminUserId\n" +
                    "   WHERE  sau.schoolId = ? and tc.type=?";
            Map<String,Object> minStartDateMap=this.findOneForJdbc(startDateSql,new Object[]{schoolId,type});

            String endDateSql="SELECT\n" +
                    "   max(tc.endDate) endDate\n" +
                    "   FROM\n" +
                    "   tss_classes tc\n" +
                    "   LEFT JOIN sys_admin_user sau ON sau.id = tc.adminUserId\n" +
                    "   WHERE  sau.schoolId = ？ and tc.type=？";
            Map<String,Object> maxEndDateMap=this.findOneForJdbc(endDateSql,new Object[]{schoolId,type});
            String minStartDate=minStartDateMap.get("startDate")+"";
            String maxEndDate=maxEndDateMap.get("endDate")+"";

            sql.append("SELECT\n" +
                    "   count(1) count \n" +
                    "   FROM\n" +
                    "   tss_classes tc\n" +
                    "   LEFT JOIN sys_admin_user sau ON sau.id = tc.adminUserId\n" +
                    "   WHERE\n" +
                    "    (( ?<= '? \n" +
                    "      and ? >= ? )\n" +
                    "    or (? >= ? \n" +
                    "       and  ? <= ? ) \n" +
                    "    or (? >= ? \n" +
                    "      and  ? <= ?))\n" +
                    "   and tc.startTime=? and tc.endTime=? and tc.startDate=? \n" +
                    "   and tc.endDate=?  and tc.roomId=? and tc.empAdminUserId=? and tc.weekDay=? \n" +
                    "   AND sau.schoolId =? and type=?");

            return this.findOneForJdbc(sql.toString(), new Object[]{startDate,minStartDate,endDate,minStartDate,
                    endDate, maxEndDate,startDate,maxEndDate,startDate,minStartDate,endDate,minStartDate,
                    startTime,endTime,startDate,endDate,roomId,empAdminUerId,weekDay,schoolId,type});



    }
}
